﻿namespace BEGDC.CC.ExcelTool
{
    #region Using Directives

    using System;
    using System.Collections;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Security.Principal;
    using System.Web.UI.HtmlControls;
    using ASBCC.Utility;
    using BL.Workflow;
    using Utility.Extensions;

    #endregion

    public static class ExcelImporter
    {
        private const string AllowedFilesTypes = "XLS,XLSX";

        public static string UploadExcelFile(HtmlInputFile inputFile)
        {
            if (inputFile == null)
            {
                throw new ArgumentNullException("inputFile");
            }

            string fullPath = string.Empty;

            if (inputFile.PostedFile != null && inputFile.PostedFile.FileName.IsNotNullOrWhiteSpace())
            {
                string fileServer = ConfigUtility.UploadServerIP;
                string ezvisitFolder = ConfigUtility.EZVisitDocument;

                string appDocRoot = fileServer + ezvisitFolder;

                GetDocumentStoredPath(appDocRoot, inputFile.PostedFile.FileName, out fullPath);

                string fileName = Path.GetFileName(fullPath).Substring(5);

                // Check if the file is allowed to be uploaded
                string ext = Path.GetExtension(fileName);

                if (string.IsNullOrEmpty(ext) || AllowedFilesTypes.IndexOf(ext.Substring(1), StringComparison.InvariantCultureIgnoreCase) < 0)
                {
                    throw new Exception("Invalid file type. Only allow " + AllowedFilesTypes + " files.");
                }

                inputFile.PostedFile.SaveAs(fullPath);
            }

            return fullPath;
        }

        public static DataTable Import(string fileName)
        {
            return Import(fileName, "Sheet1$");
        }

        public static DataTable Import(string fileName, string sheetName)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentException("Target Excel file name cannot be empty.");
            }

            string ext = Path.GetExtension(fileName);

            if (string.IsNullOrEmpty(ext) || AllowedFilesTypes.IndexOf(ext.Substring(1), StringComparison.InvariantCultureIgnoreCase) < 0)
            {
                throw new Exception("Invalid file type. Only allow " + AllowedFilesTypes + " files.");
            }

            string connectionString;

            if (ext.VariantEquals(".xlsx"))
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties=\"Excel 12.0 Xml; HDR=YES; IMEX=1;\"";
            }
            else
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
            }

            using (var conn = new OleDbConnection(connectionString))
            {
                conn.Open();

                var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null || dt.Rows.Count == 0)
                {
                    throw new Exception("No worksheets fount in the Excel file.");
                }

                string sheet= sheetName;

                if (sheet == null || sheet.Trim().Length == 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        sheet = row["TABLE_NAME"].ToString();

                        if (sheet.IsNotNullOrWhiteSpace() && sheet.EndsWith("$"))
                        {
                            break;
                        }
                    }
                }

                var cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);

                using (var adapter = new OleDbDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        private static void GetDocumentStoredPath(string rootPath, string fileName, out string fullPath)
        {
            string path = Path.Combine(rootPath, DateTime.Now.ToString("yyMMdd"));

            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            string prefix = GetDocFilePrefix(path);

            fullPath = Path.Combine(path, prefix + "_" + Path.GetFileName(fileName));
        }

        private static string GetDocFilePrefix(string path)
        {
            int max = 0;
            string[] files = Directory.GetFiles(path);
            if (files.Length > 0)
            {
                Array.Sort(files);
                string name = Path.GetFileName(files[files.Length - 1]);
                string prefix = name.Substring(0, 4);
                max = Convert.ToInt32(prefix);
            }
            ++max;
            return max.ToString("d4");
        }
    }
}